IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'MD3.f_GetIdxKeysCSV'))
  DROP FUNCTION MD3.f_GetIdxKeysCSV
GO

--returns a CSV list of keys for the given index

CREATE FUNCTION MD3.f_GetIdxKeysCSV
(
  @SchemaName  SYSNAME,
  @TblName     SYSNAME,
  @IdxName     SYSNAME
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE
      @ColList VARCHAR(MAX),
      @FullName SYSNAME

  SELECT @FullName = @SchemaName + '.' + @TblName

  SELECT  @ColList =
    (SELECT CASE
              WHEN sic.key_ordinal > 1 THEN ','
              ELSE ''
            END +
            sc.name + 
            CASE
                WHEN sic.is_descending_key = 1 THEN '(-)'
                ELSE ''
            END
       FROM sys.tables st 
       JOIN sys.indexes si
         ON st.object_id = si.object_id
       JOIN sys.index_columns sic
         ON st.object_id = sic.object_id
        AND si.index_id = sic.index_id 
       JOIN sys.columns sc 
         ON st.object_id = sc.object_id
        AND sic.column_id = sc.column_id 
      WHERE st.object_id = OBJECT_ID(@FullName)
        AND si.name = @IdxName
		AND sic.is_included_column = 0
   ORDER BY sic.key_ordinal  
     FOR XML PATH(''))

  RETURN @ColList
END
GO

